package com.crm.business.crm.service.impl;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import com.crm.business.admin.service.AdminService;
import com.crm.common.utils.spring.SpringUtils;
import com.csvreader.CsvReader;
import cn.hutool.core.util.IdUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.crm.business.crm.mapper.CrmContactsMapper;
import com.crm.business.crm.service.ComparisonExcelService;
import com.crm.business.crm.service.ICrmContactsService;
import com.crm.business.crm.service.ICrmCustomerService;
import com.crm.common.ka.core.feign.admin.entity.AdminMessage;
import com.crm.common.crm.common.CrmExcelUtil;
import com.crm.common.crm.constant.FieldEnum;
import com.crm.common.crm.entity.BO.UploadExcelBO;
import com.crm.common.crm.entity.PO.CrmCustomer;
import com.crm.common.crm.entity.VO.CrmModelFiledVO;
import com.crm.common.ka.core.common.SystemCodeEnum;
import com.crm.common.ka.core.common.cache.AdminCacheKey;
import com.crm.common.ka.core.exception.CrmException;
import com.crm.common.ka.core.feign.admin.entity.AdminMessageEnum;
import com.crm.common.ka.core.redis.Redis;
import com.crm.business.servlet.ApplicationContextHolder;
import com.crm.common.ka.core.servlet.upload.UploadConfig;
import com.crm.business.utils.BaseUtil;
import com.crm.common.ka.core.utils.CompressUtil;
import com.crm.business.utils.UserUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.task.TaskExecutor;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.Charset;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Service
@Slf4j
public class ComparisonExcelServiceImpl implements ComparisonExcelService {

    @Autowired
    private AdminService adminService;
    @Autowired
    private Redis redis;
    @Autowired
    private ICrmContactsService crmContactsService;
    @Autowired
    private CrmContactsMapper crmContactsMapper;

    /**
     * 上传excel可查询时间
     */
    protected static final int UPLOAD_EXCEL_EXIST_TIME = 7200;

    private final String uploadPath = SpringUtils.getBean(UploadConfig.class).getLocal().getUploadPath().get("0");
    private Long messageId;
    private File[] files;

    private final String model = SpringUtils.getBean(UploadConfig.class).getLocal().getUploadPath().get("2");

    @Override
    public Long uploadExcel(MultipartFile file, UploadExcelBO uploadExcelBO) throws IOException {

        String filePath = getFilePath(file);
        uploadExcelBO.setFilePath(filePath);
        AdminMessage adminMessage = new AdminMessage();
        adminMessage.setCreateUser(UserUtil.getUserId());
        adminMessage.setRecipientUser(UserUtil.getUserId());
        adminMessage.setLabel(6);
        adminMessage.setType(AdminMessageEnum.COMPARISON_EXCEL_IMPORT.getType());
        Long messageId = adminService.saveOrUpdateMessage(adminMessage);
        this.messageId = messageId;
        uploadExcelBO.setMessageId(messageId);
        redis.setex(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + messageId.toString(), UPLOAD_EXCEL_EXIST_TIME, 0);

        Handle handle = new Handle(file, uploadExcelBO);
        TaskExecutor taskExecutor = SpringUtils.getBean(TaskExecutor.class);
        taskExecutor.execute(handle);

        return messageId;
    }

    public void handleCvsFile(UploadExcelBO uploadExcelBO) throws IOException {
        List<CrmModelFiledVO> crmModelFiledList = crmContactsService.queryField(null);
        crmModelFiledList.removeIf(model -> Arrays.asList(FieldEnum.FILE, FieldEnum.CHECKBOX, FieldEnum.USER, FieldEnum.STRUCTURE).contains(FieldEnum.parse(model.getType())));
        /*HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("联系人导入表");
        this.handleWbAndSheet(crmModelFiledList, wb, sheet);

        HSSFWorkbook wb2 = new HSSFWorkbook();
        HSSFSheet sheet2 = wb2.createSheet("联系人导入表");
        this.handleWbAndSheet(crmModelFiledList, wb2, sheet2);*/


        File model = new File(this.model);
        File tempFile = this.getFile("contacts_import_" + IdUtil.simpleUUID());
        File tempFile2 = this.getFile("contacts_import_error_" + IdUtil.simpleUUID());

        tempFile = FileUtil.copy(model, tempFile, true);
        tempFile2 = FileUtil.copy(model, tempFile2, true);
        HSSFWorkbook wb = new HSSFWorkbook(FileUtil.getInputStream(tempFile));
        HSSFSheet sheet = wb.getSheet("联系人导入表");

        HSSFWorkbook wb2 = new HSSFWorkbook(FileUtil.getInputStream(tempFile2));
        HSSFSheet sheet2 = wb2.getSheet("联系人导入表");

        /*File tempFile = null;
        File tempFile2 = null;*/
        try {
//            this.handleExcel(crmModelFiledList, wb, sheet);
//            this.handleExcel(crmModelFiledList, wb2, sheet2);

            //给模版写入数据
            Map<String, List<List<Object>>> map = this.getDataByCsv(uploadExcelBO);
            List<List<Object>> valid = map.get("valid");
            List<List<Object>> invalid = map.get("invalid");
            this.assignValue(sheet, valid,uploadExcelBO);
            this.assignValue(sheet2, invalid,uploadExcelBO);

            /*tempFile = this.getFile("contacts_import_" + IdUtil.simpleUUID());
            wb.write(tempFile);
            tempFile2 = this.getFile("contacts_import_error_" + IdUtil.simpleUUID());
            wb2.write(tempFile2);*/
            wb.write(tempFile);
            wb2.write(tempFile2);
            this.files = new File[]{tempFile, tempFile2};
        } catch (Exception e) {
            log.error("联系人模板下载错误：", e);
        } finally {
            wb.close();
            wb2.close();
        }
    }

    /**
     * 给模版excel写入数据
     *
     * @param sheet
     * @param valid
     */
    private void assignValue(HSSFSheet sheet, List<List<Object>> valid, UploadExcelBO uploadExcelBO) {
        for (int i = 0; i < valid.size(); i++) {
            HSSFRow rowData = sheet.createRow(i + 2);
            List<Object> list = valid.get(i);

            //映射数据 -- 姓名
            HSSFCell cell = rowData.createCell(0);
            String name = (String) list.get(1);
            cell.setCellValue(name);
            //学员姓名
            HSSFCell cell1 = rowData.createCell(1);
            //系统编号
            HSSFCell cell2 = rowData.createCell(2);
            Object o = null;
            try {
                o = list.get(31);
                CrmCustomer custom = (CrmCustomer) o;
                cell1.setCellValue(custom.getCustomerName());
                cell2.setCellValue(custom.getSerialNumber());
            } catch (Exception ignored) {
            }
            if (o == null) {
                cell1.setCellValue("");
                cell2.setCellValue("");
            }
            //回款状态
            HSSFCell cell3 = rowData.createCell(3);
            cell3.setCellValue("");
            //vip期数
            HSSFCell cell4 = rowData.createCell(4);
            cell4.setCellValue(uploadExcelBO.getVipPeriods());
            //性别
            HSSFCell cell5 = rowData.createCell(5);
            cell5.setCellValue((String) list.get(5));
            //年龄
            HSSFCell cell6 = rowData.createCell(6);
            cell6.setCellValue((String) list.get(6));
            //电话
            HSSFCell cell7 = rowData.createCell(7);
            cell7.setCellValue((String) list.get(2));
            //微信
            HSSFCell cell8 = rowData.createCell(8);
            cell8.setCellValue((String) list.get(3));
            //QQ
            HSSFCell cell9 = rowData.createCell(9);
            cell9.setCellValue((String) list.get(4));
            //训练营期数
            HSSFCell cell10 = rowData.createCell(10);
            try {
                o = list.get(31);
                CrmCustomer custom = (CrmCustomer) o;
                cell10.setCellValue(custom.getTrainingPeriods());
            } catch (Exception ignored) {
                cell10.setCellValue("");
            }
            //复购
            HSSFCell cell12 = rowData.createCell(11);
            cell12.setCellValue("");
            //转介绍
            HSSFCell cell13 = rowData.createCell(12);
            cell13.setCellValue("");
            //转介绍人
            HSSFCell cell14 = rowData.createCell(13);
            cell14.setCellValue("");
            //休学
            HSSFCell cell15 = rowData.createCell(14);
            cell15.setCellValue("");
            //转期
            HSSFCell cell16 = rowData.createCell(15);
            cell16.setCellValue("");
            //转前期数
            HSSFCell cell17 = rowData.createCell(16);
            cell17.setCellValue("");
            //重学
            HSSFCell cell18 = rowData.createCell(17);
            cell18.setCellValue("");
            //在读/最高学历
            HSSFCell cell19 = rowData.createCell(18);
            cell19.setCellValue((String) list.get(10));
            //就读/毕业院校
            HSSFCell cell20 = rowData.createCell(19);
            cell20.setCellValue((String) list.get(8));
            //所学专业
            HSSFCell cell21 = rowData.createCell(20);
            cell21.setCellValue((String) list.get(9));
            //毕业时间
            HSSFCell cell22 = rowData.createCell(21);
            String graduation = (String) list.get(11);
            String[] graduations = graduation.split(":");
            if (graduations.length == 2 && !StringUtils.isEmpty(graduations[1])) {
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                try {
                    Date date = simpleDateFormat.parse(graduations[1]);
                    String format = DateUtil.format(date, "yyyy-MM-dd");
                    cell22.setCellValue(format);
                } catch (ParseException e) {
                    e.printStackTrace();
                    cell22.setCellValue("");
                }
            } else {
                cell22.setCellValue("");
            }

            //目前就业行业
            HSSFCell cell23 = rowData.createCell(22);
            cell23.setCellValue((String) list.get(23));
            //目前就业薪资
            HSSFCell cell24 = rowData.createCell(23);
            cell24.setCellValue((String) list.get(25));
            //设计水平
            HSSFCell cell25 = rowData.createCell(24);
            cell25.setCellValue((String) list.get(17));
            //设计工作年限
            HSSFCell cell26 = rowData.createCell(25);
            cell26.setCellValue((String) list.get(16));
            //毕业状态
            HSSFCell cell28 = rowData.createCell(26);
            cell28.setCellValue((String) list.get(12));
            //接单状态
            HSSFCell cell29 = rowData.createCell(27);
            cell29.setCellValue("");
            //就业状态
            HSSFCell cell30 = rowData.createCell(28);
            cell30.setCellValue((String) list.get(21));
            //就业薪资
            HSSFCell cell31 = rowData.createCell(29);
            cell31.setCellValue((String) list.get(29));
            //就业企业
            HSSFCell cell33 = rowData.createCell(30);
            cell33.setCellValue((String) list.get(27));
            //期望就业企业规模
            HSSFCell cell34 = rowData.createCell(31);
            cell34.setCellValue((String) list.get(30));
            //入职时间
            HSSFCell cell35 = rowData.createCell(32);
            String onBoarding = (String) list.get(22);
            String[] strings = onBoarding.split(":");
            if (strings.length == 2 && !StringUtils.isEmpty(strings[1])) {
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                try {
                    Date date = simpleDateFormat.parse(strings[1]);
                    String format = DateUtil.format(date, "yyyy-MM-dd");
                    cell35.setCellValue(format);
                } catch (ParseException e) {
                    e.printStackTrace();
                    cell35.setCellValue("");
                }
            } else {
                cell35.setCellValue("");
            }
            //备注
            HSSFCell cell36 = rowData.createCell(33);
            cell36.setCellValue("");

            //省
            HSSFCell cell37 = rowData.createCell(34);
            //市
            HSSFCell cell38 = rowData.createCell(35);
            //区
            HSSFCell cell39 = rowData.createCell(36);
            String s = (String) list.get(7);
            String[] split = s.split("\\|");
            if (split.length == 3) {
                cell37.setCellValue(StringUtils.isEmpty(split[0]) ? "" : split[0]);
                cell38.setCellValue(StringUtils.isEmpty(split[1]) ? "" : split[1]);
                cell39.setCellValue(StringUtils.isEmpty(split[2]) ? "" : split[2]);
            } else {
                cell37.setCellValue("");
                cell38.setCellValue("");
                cell39.setCellValue("");
            }

            /**--------------------------**/

            //目前就业城市
            HSSFCell cell32_1 = rowData.createCell(37);
            HSSFCell cell32_2 = rowData.createCell(38);
            HSSFCell cell32_3 = rowData.createCell(39);
            String s1 = (String) list.get(24);
            String[] split1 = s1.split("\\|");
            if (split1.length == 3) {
                cell32_1.setCellValue(StringUtils.isEmpty(split1[0]) ? "" : split1[0]);
                cell32_2.setCellValue(StringUtils.isEmpty(split1[1]) ? "" : split1[1]);
                cell32_3.setCellValue(StringUtils.isEmpty(split1[2]) ? "" : split1[2]);
            } else if(split1.length == 2) {
                cell32_1.setCellValue(StringUtils.isEmpty(split1[0]) ? "" : split1[0]);
                cell32_2.setCellValue(StringUtils.isEmpty(split1[1]) ? "" : split1[1]);
                cell32_3.setCellValue("");
            }else if(split1.length == 1){
                cell32_1.setCellValue(StringUtils.isEmpty(split1[0]) ? "" : split1[0]);
                cell32_2.setCellValue("");
                cell32_3.setCellValue("");
            }else{
                cell32_1.setCellValue("");
                cell32_2.setCellValue("");
                cell32_3.setCellValue("");
            }

            //期望就业城市
            HSSFCell cell32_1_qiwang = rowData.createCell(40);
            HSSFCell cell32_2_qiwang = rowData.createCell(41);
            HSSFCell cell32_3_qiwang = rowData.createCell(42);
            String s1_qiwang = (String) list.get(28);
            String[] split1_qiwang = s1_qiwang.split("\\|");
            if (split1_qiwang.length == 3) {
                cell32_1_qiwang.setCellValue(StringUtils.isEmpty(split1_qiwang[0]) ? "" : split1_qiwang[0]);
                cell32_2_qiwang.setCellValue(StringUtils.isEmpty(split1_qiwang[1]) ? "" : split1_qiwang[1]);
                cell32_3_qiwang.setCellValue(StringUtils.isEmpty(split1_qiwang[2]) ? "" : split1_qiwang[2]);
            } else if(split1.length == 2) {
                cell32_3_qiwang.setCellValue(StringUtils.isEmpty(split1[0]) ? "" : split1[0]);
                cell32_3_qiwang.setCellValue(StringUtils.isEmpty(split1[1]) ? "" : split1[1]);
                cell32_3_qiwang.setCellValue("");
            }else if(split1.length == 1){
                cell32_3_qiwang.setCellValue(StringUtils.isEmpty(split1[0]) ? "" : split1[0]);
                cell32_3_qiwang.setCellValue("");
                cell32_3_qiwang.setCellValue("");
            }else{
                cell32_3_qiwang.setCellValue("");
                cell32_3_qiwang.setCellValue("");
                cell32_3_qiwang.setCellValue("");
            }



            //类型
            HSSFCell cell40 = rowData.createCell(43);
            cell40.setCellValue("");
            //是否定稿
            HSSFCell cell41 = rowData.createCell(44);
            cell41.setCellValue("");
            //派单数量
            HSSFCell cell42 = rowData.createCell(45);
            cell42.setCellValue("");
            //派单定稿数量
            HSSFCell cell43 = rowData.createCell(46);
            cell43.setCellValue("");
            //重修期数
            HSSFCell cell44 = rowData.createCell(47);
            cell44.setCellValue("");
            //目前所处状态
            HSSFCell cell45 = rowData.createCell(48);
            cell45.setCellValue((String) list.get(15));
            //目前就业企业规模
            HSSFCell cell46 = rowData.createCell(49);
            cell46.setCellValue((String) list.get(26));
            //学习目的
            HSSFCell cell50 = rowData.createCell(50);
            String learningPurpose = String.valueOf(list.get(14));
            learningPurpose = learningPurpose.replace("|",",");
            cell50.setCellValue(learningPurpose);
            //期待课程
            HSSFCell cell51 = rowData.createCell(51);
            String expectationCourse = String.valueOf(list.get(18));
            expectationCourse = expectationCourse.replace("|",",");
            cell51.setCellValue(expectationCourse);
            //报名因素
            HSSFCell cell52 = rowData.createCell(52);
            String registrationFactors = String.valueOf(list.get(19));
            registrationFactors = registrationFactors.replace("|",",");
            cell52.setCellValue(registrationFactors);
            //可支配时间
            HSSFCell cell53 = rowData.createCell(53);
            String disposableTime = String.valueOf(list.get(20));
            disposableTime = disposableTime.replace("|",",");
            cell53.setCellValue(disposableTime);


        }
    }

    private void handleWbAndSheet(List<CrmModelFiledVO> crmModelFiledList, HSSFWorkbook wb, HSSFSheet sheet) {
        sheet.setDefaultRowHeight((short) 400);
        CellStyle textStyle = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        textStyle.setDataFormat(format.getFormat("@"));
        for (int i = 0; i < crmModelFiledList.size(); i++) {
            CrmModelFiledVO crmModel = crmModelFiledList.get(i);
            if (FieldEnum.DATE.getType().equals(crmModel.getType())) {
                CellStyle dateStyle = wb.createCellStyle();
                DataFormat dateFormat = wb.createDataFormat();
                dateStyle.setDataFormat(dateFormat.getFormat(DatePattern.NORM_DATE_PATTERN));
                sheet.setDefaultColumnStyle(i, dateStyle);
            } else if (FieldEnum.DATETIME.getType().equals(crmModel.getType())) {
                CellStyle dateStyle = wb.createCellStyle();
                DataFormat dateFormat = wb.createDataFormat();
                dateStyle.setDataFormat(dateFormat.getFormat(DatePattern.NORM_DATETIME_PATTERN));
                sheet.setDefaultColumnStyle(i, dateStyle);
            } else {
                sheet.setDefaultColumnStyle(i, textStyle);
            }
            sheet.setColumnWidth(i, 20 * 256);
        }
        HSSFRow titleRow = sheet.createRow(0);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 16);
        cellStyle.setFont(font);
        titleRow.createCell(0).setCellValue("联系人导入模板(*)为必填项");
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleRow.getCell(0).setCellStyle(cellStyle);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, crmModelFiledList.size() + 1);
        sheet.addMergedRegion(region);
    }

    private void handleExcel(List<CrmModelFiledVO> crmModelFiledList, HSSFWorkbook wb, HSSFSheet sheet) {
        HSSFRow row = sheet.createRow(1);
        for (int i = 0; i < crmModelFiledList.size(); i++) {
            CrmModelFiledVO record = crmModelFiledList.get(i);
            String fieldName = "_" + record.getFieldName();
            fieldName = fieldName.replaceAll("[\\n`~!@#$%^&*()+=|{}':;',\\\\[\\\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。， 、？]", "");
            //省市区需特殊处理
            if ("_mapAddress".equals(fieldName)) {
                HSSFCell cell1 = row.createCell(i);
                cell1.setCellValue("省");
                HSSFCell cell2 = row.createCell(i + 1);
                cell2.setCellValue("市");
                HSSFCell cell3 = row.createCell(i + 2);
                cell3.setCellValue("区");
                HSSFSheet hideSheet = wb.createSheet(fieldName);
                wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);
                int rowId = 0;
                // 设置第一行，存省的信息
                Row provinceRow = hideSheet.createRow(rowId++);
                provinceRow.createCell(0).setCellValue("省列表");
                List<String> provinceList = crmContactsMapper.queryCityList(100000);
                for (int x = 0; x < provinceList.size(); x++) {
                    Cell provinceCell = provinceRow.createCell(x + 1);
                    provinceCell.setCellValue(provinceList.get(x));
                }
                // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
                Map<String, List<String>> areaMap = CrmExcelUtil.getAreaMap();
                for (String key : areaMap.keySet()) {
                    List<String> son = areaMap.get(key);
                    Row subRow = hideSheet.createRow(rowId++);
                    subRow.createCell(0).setCellValue(key);
                    for (int x = 0; x < son.size(); x++) {
                        Cell cell = subRow.createCell(x + 1);
                        cell.setCellValue(son.get(x));
                    }
                    // 添加名称管理器
                    String range = CrmExcelUtil.getRange(1, rowId, son.size());
                    Name name = wb.createName();
                    // key不可重复
                    name.setNameName(key);
                    String formula = fieldName + "!" + range;
                    name.setRefersToFormula(formula);
                }
                // 省级下拉框
                CellRangeAddressList provRangeAddressList = new CellRangeAddressList(2, Integer.MAX_VALUE, i, i);
                String[] arr = provinceList.toArray(new String[]{});
                DVConstraint provConstraint = DVConstraint.createExplicitListConstraint(arr);
                HSSFDataValidation provinceDataValidation = new HSSFDataValidation(provRangeAddressList, provConstraint);
                provinceDataValidation.createErrorBox("error", "请选择正确的省份");
                sheet.addValidationData(provinceDataValidation);
                //市 区下拉框
                for (int x = 2; x < 10000; x++) {
                    CrmExcelUtil.setDataValidation(CrmExcelUtil.getCorrespondingLabel(i + 1), sheet, x, i + 1);
                    CrmExcelUtil.setDataValidation(CrmExcelUtil.getCorrespondingLabel(i + 2), sheet, x, i + 2);
                }
            } else {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(record.getName() + (record.getIsNull() == 1 ? "(*)" : ""));
                List<String> setting = record.getSetting().stream().map(Object::toString).collect(Collectors.toList());
                if (setting != null && setting.size() != 0) {
                    HSSFSheet hidden = wb.createSheet(fieldName);
                    HSSFCell sheetCell = null;
                    for (int j = 0, length = setting.size(); j < length; j++) {
                        String name = setting.get(j);
                        HSSFRow sheetRow = hidden.createRow(j);
                        sheetCell = sheetRow.createCell(0);
                        sheetCell.setCellValue(name);
                    }
                    Name namedCell = wb.createName();
                    namedCell.setNameName(fieldName);
                    namedCell.setRefersToFormula(fieldName + "!$A$1:$A$" + setting.size());
                    CellRangeAddressList regions = new CellRangeAddressList(2, Integer.MAX_VALUE, i, i);
                    DVConstraint constraint = DVConstraint.createFormulaListConstraint(fieldName);
                    HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
                    wb.setSheetHidden(wb.getSheetIndex(hidden), true);
                    sheet.addValidationData(dataValidation);
                }
            }
        }
    }

    /**
     * 导入excel
     */
    public Map<String, List<List<Object>>> getDataByCsv(UploadExcelBO uploadExcelBO) {

        Map<String, List<List<Object>>> map = new HashMap<>();

        List<List<Object>> lists = new ArrayList<>();
        List<List<Object>> errorList = new ArrayList<>();

        String filePath = uploadExcelBO.getFilePath();
        QueryWrapper<CrmCustomer> queryWrapper = new QueryWrapper<>();
        queryWrapper.ne("status", 3);
        queryWrapper.eq("vip_deal_status", "已成交");
        queryWrapper.eq("assistant_user_id",UserUtil.getUserId());
//        queryWrapper.isNull("contacts_id");
//        queryWrapper.eq("contract_id", contractId);
//        queryWrapper.and(wrapper -> wrapper.eq("contacts_id", "").or().eq("contacts_id", null));
        List<CrmCustomer> customerList = SpringUtils.getBean(ICrmCustomerService.class).getBaseMapper().selectList(queryWrapper);
//        List<CrmCustomer> customerList = SpringUtils.getBean(ICrmCustomerService.class).list();

        if (!CollectionUtils.isEmpty(customerList)) {
            try {
                // 创建CSV读对象
                CsvReader csvReader = new CsvReader(new FileInputStream(filePath), Charset.forName("GBK"));
//                CsvReader csvReader = new CsvReader(filePath);

                // 读表头
                csvReader.readHeaders();
                String[] headers = csvReader.getHeaders();
//                log.info("表头: {}", Arrays.toString(headers));

                while (csvReader.readRecord()) {
                    // 读一整行
                    //System.out.println(csvReader.getRawRecord());
                    List<Object> list = new ArrayList<>();
                    // 一个一个读
                    for (int i = 0; i < headers.length; i++) {
                        list.add(csvReader.get(i));
                    }

                    String telephone = csvReader.get(2).trim();
                    String wechat = csvReader.get(3).trim();
                    Optional<CrmCustomer> customerOptional = customerList.stream().filter(item -> telephone.equals(item.getTelephone()) ||
                            wechat.equals(item.getWechat())
                    ).findFirst();



                    CrmCustomer customer;
                    if (!customerOptional.isPresent()) {
                        errorList.add(list);
                    } else {
                        customer = customerOptional.get();
                        log.info("学员：{}", customer);
                        list.add(customer);
                        lists.add(list);
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                FileUtil.del(filePath);
            }
        }

        map.put("valid", lists);
        map.put("invalid", errorList);
        return map;
    }

    /**
     * 异步执行解析cvs
     */
    public class Handle implements Runnable {

        MultipartFile file;
        UploadExcelBO uploadExcelBO;

        public Handle(MultipartFile file, UploadExcelBO uploadExcelBO) {
            this.file = file;
            this.uploadExcelBO = uploadExcelBO;
        }

        @Override
        public void run() {
            try {
                UserUtil.setUser(uploadExcelBO.getUserInfo());
                handleCvsFile(uploadExcelBO);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                redis.del(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + uploadExcelBO.getMessageId());
                UserUtil.removeUser();
            }

            AdminMessage adminMessage = adminService.getMessageById(messageId);
            adminMessage.setTitle("1");
            adminMessage.setContent("1,0");

            String fileName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
            File f = new File(uploadPath + "/zip/" + BaseUtil.getDate() + "/compare_" + fileName + "_" + IdUtil.simpleUUID() + ".zip");
            try {
                CompressUtil.zipFiles(files, f);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                for (File tempFile : files) {
                    FileUtil.del(tempFile);
                }
            }

            adminMessage.setTypeId(null);
            //将错误信息的excel保存七天 604800 七天的秒数
            redis.setex(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + "file:" + messageId.toString(), 604800, f.getAbsolutePath());

            adminService.saveOrUpdateMessage(adminMessage);
        }
    }

    /**
     * 获取临时文件路径,用完记得删除
     *
     * @param file file
     * @return path
     */
    private String getFilePath(MultipartFile file) {
        String dirPath = FileUtil.getTmpDirPath();
        try {
            InputStream inputStream = file.getInputStream();
            File fromStream = FileUtil.writeFromStream(inputStream, dirPath + "/" + IdUtil.simpleUUID() + file.getOriginalFilename());
            return fromStream.getAbsolutePath();
        } catch (IOException e) {
            throw new CrmException(SystemCodeEnum.SYSTEM_UPLOAD_FILE_ERROR);
        }
    }

    /**
     * 获取临时输出流
     */
    private File getFile(String fileName) {
        String dirPath = FileUtil.getTmpDirPath();
        File file = new File(dirPath + "/" + fileName + ".xls");
        return file;
    }
}
